package edu.geo.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.postgis.PGgeometry;

import edu.geo.containers.geom.impl.GeometryFactory;
import edu.geo.containers.geom.impl.LineSegment;
import edu.geo.containers.geom.impl.Point;
import edu.geo.dao.IGeometryDao;
import edu.geo.dao.exception.PersistentExceptions;
import edu.geo.db.conn.DBConnection;
import edu.geo.db.conn.DBConnectionProvider;
import edu.geo.exception.GeoInfoException;

/**
 * 
 * @author alejandro.sanchez
 *
 */
public class GeometryDao implements IGeometryDao {
	/**
	 * 
	 */
	private static final long serialVersionUID = -4639354614196469064L;

	private static final String EXISTS_POINT = "SELECT fe_id FROM geometric_domains.point WHERE po_geom = ''{0}''";
	
	private static final String EXISTS_LINE_BY_POINT_ID = "SELECT fe_id FROM geometric_domains.line_segment WHERE ls_begin_node = {0} AND ls_end_node = {1}";
	
	private static final String INSERT_POINT = "SELECT insertPoint(''{0}'')";
	
	private static final String INSERT_LINE_SEGMENT = "SELECT insertLineSegment( ''{0}'', ''{1}'')";
	
	private static final String LINE_SEGMENT_FORM = "({0}, {1})";
	
	private static final String RETRIEVE_POINTS = "SELECT * FROM geometric_domains.point WHERE fe_id IN (SELECT fe_id FROM  geometric_domains.geometric_domain_point WHERE gd_id = {0})";
	
	private static final String DELETE_UNREF_POINTS = "DELETE FROM geometric_domains.point USING geometric_domains.geometric_domain_point WHERE " +
                                                         " geometric_domains.point.fe_id NOT IN ( SELECT DISTINCT geometric_domains.geometric_domain_point.fe_id FROM  geometric_domains.geometric_domain_point " +
                                                         "   UNION " +
                                                         "   SELECT DISTINCT gr_origin FROM  geometric_domains.grid " + 
                                                         "   UNION " +
                                                         "   SELECT DISTINCT ls_begin_node FROM  geometric_domains.line_segment " + 
                                                         "   UNION " +
                                                         "   SELECT DISTINCT ls_end_node FROM  geometric_domains.line_segment ); ";
	private static final String DELETE_UNREF_LINE_SEGMENTS = "DELETE FROM geometric_domains.line_segment USING geometric_domains.geometric_domain_line_segment " +
			"WHERE geometric_domains.line_segment.fe_id NOT IN " +
			"( SELECT DISTINCT geometric_domains.geometric_domain_line_segment.fe_id AS id FROM  geometric_domains.geometric_domain_line_segment)";
	
	private static final String RETRIEVE_LINE_SEGMENTS = "WITH line_segment AS (SELECT fe_id, ls_begin_node, ls_end_node FROM geometric_domains.line_segment WHERE fe_id IN (SELECT fe_id FROM  geometric_domains.geometric_domain_line_segment WHERE gd_id = {0}))" +
	" SELECT fe_id, begin_node_id, begin_node_geom, end_node_id, end_node_geom FROM " +
	" (SELECT p1.fe_id AS begin_node_id, p1.po_geom AS begin_node_geom, p2.fe_id AS end_node_id, p2.po_geom AS end_node_geom" + 
	" FROM geometric_domains.point p1, geometric_domains.point p2) AS a JOIN line_segment" +
	" ON begin_node_id = ls_begin_node AND  end_node_id = ls_end_node";
	
	private static final String RETRIEVE_LINE_SEGMENT = "WITH line_segment AS (SELECT fe_id, ls_begin_node, ls_end_node FROM geometric_domains.line_segment WHERE fe_id = {0})" +
			"SELECT p1.fe_id AS begin_node_id, p1.po_geom AS begin_node_geom, p2.fe_id AS end_node_id, p2.po_geom AS end_node_geom FROM geometric_domains.point p1, geometric_domains.point p2" +
			"WHERE p1.fe_id = (select ls_begin_node FROM line_segment) AND p2.fe_id = (select ls_end_node FROM line_segment)";
	
	private static final String RETRIEVE_POINT_BY_ID = "SELECT po_geom FROM geometric_domains.point WHERE fe_id = {0}";
	
	static private final Log LOGGER = LogFactory.getLog(GeometryDao.class);
	
	
	private DBConnectionProvider connectionProvider;
	
	public GeometryDao(DBConnectionProvider connectionProvider) {
		super();
		this.connectionProvider = connectionProvider;
	}

	@Override
	public int insertPoint(Point point) throws GeoInfoException {
		Connection conn = null;
		Statement statement = null;
		ResultSet resultSet= null;
		//Verifica que el punto esté definido
		if ( point == null )
		{
			throw PersistentExceptions.POINT_IS_NULL.getExcepcion(LOGGER, null);
		}		
		//Verifica que no se haya especificado un id, porque estos son asignados por el modelo de datos.
		if ( point.getId() > 0 )
		{
			throw PersistentExceptions.WRONG_INSERT_POINT.getExcepcion(LOGGER, null);
		}
		try {
			int id = verifyPointExistence(point);
			if (id > 0)
			{
				throw PersistentExceptions.POINT_EXISTS.getExcepcion(LOGGER, null, point.toString(), id);
			}
			else
			{
				conn = connectionProvider.getConnection();
				statement = conn.createStatement();
				resultSet = statement.executeQuery(MessageFormat.format(INSERT_POINT, point.getAsWKT()));
				if (resultSet.next())
				{
					point.setId(resultSet.getInt(1));
					return resultSet.getInt(1);
				}
				else 
				{
					throw PersistentExceptions.INSERT_ERROR.getExcepcion(LOGGER, null, point.toString());
				}
			}			
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}	
		finally
		{
			DBConnection.close( resultSet, statement, conn );
		}
	}
	
	
	@Override
	public int insertLineSegment(LineSegment lineSegment) throws GeoInfoException {
		Connection conn = null;
		Statement statement = null;
		ResultSet resultSet= null;
		int beginNodeId = Point.DEFAULT_POINT_ID;
		int endNodeId = Point.DEFAULT_POINT_ID;
		int lineSegmentId = LineSegment.DEFAULT_LINE_SEGMENT_ID;
		if ( lineSegment == null )
		{
			throw PersistentExceptions.LINE_SEGMENT_IS_NULL.getExcepcion(LOGGER, null);
		}
		//Verifica que no se haya especificado un id, porque estos son asignados por el modelo de datos.
		if ( lineSegment.getId() > 0 )
		{
			throw PersistentExceptions.WRONG_INSERT_LINE_SEGMENT.getExcepcion(LOGGER, null);
		}
		
		try {			
			//Verificar existencia nodo inicial si es diferente de null
			if ( lineSegment.getBeginNode() != null 
					&& lineSegment.getBeginNode().getId() == Point.DEFAULT_POINT_ID )
			{
				beginNodeId = verifyPointExistence(lineSegment.getBeginNode());
				if (beginNodeId < 0)
				{
					beginNodeId = insertPoint(lineSegment.getBeginNode());
				}
				lineSegment.getBeginNode().setId(beginNodeId);				
			}	
			else
			{
				beginNodeId = lineSegment.getBeginNode().getId();
			}
			//Verificar existencia nodo final si es diferente de null
			if ( lineSegment.getEndNode() != null 
					&& lineSegment.getEndNode().getId() == Point.DEFAULT_POINT_ID )
			{
				endNodeId = verifyPointExistence(lineSegment.getEndNode());
				if (endNodeId < 0)
				{
					endNodeId = insertPoint(lineSegment.getEndNode());
				}
				lineSegment.getEndNode().setId(endNodeId);				
			}
			else
			{
				endNodeId = lineSegment.getEndNode().getId();
			}
			if ( beginNodeId != Point.DEFAULT_POINT_ID
					  &&  endNodeId != Point.DEFAULT_POINT_ID )
			{								
				//Insertar nuevo lineSegment
				lineSegmentId = verifyLineSegmentExistenceByid(lineSegment);
				if (lineSegmentId > 0)
				{
					throw PersistentExceptions.LINE_SEGMENT_EXISTS.getExcepcion(LOGGER, null, lineSegment.toString(), lineSegmentId);
				}
				else
				{
					conn = connectionProvider.getConnection();
					statement = conn.createStatement();
					resultSet = statement.executeQuery(MessageFormat.format(INSERT_LINE_SEGMENT, beginNodeId, endNodeId));
					if (resultSet.next())
					{
						lineSegmentId = resultSet.getInt(1);
					}
					else 
					{
						throw PersistentExceptions.INSERT_ERROR.getExcepcion(LOGGER, null, MessageFormat.format(LINE_SEGMENT_FORM, lineSegment.getBeginNode().getId(), lineSegment.getEndNode().getId()));
					}
				}
			}
			lineSegment.setId(lineSegmentId);
			return lineSegmentId;			
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}	
		finally
		{
			DBConnection.close( resultSet, statement, conn);
		}
	}

	@Override
	public int verifyPointExistence(Point feature) throws GeoInfoException {
		Connection conn = null;
		Statement statement = null;
		ResultSet result = null;
		if ( feature == null )
		{
			throw PersistentExceptions.POINT_IS_NULL.getExcepcion(LOGGER, null);
		}
		try {
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			result = statement.executeQuery(MessageFormat.format(EXISTS_POINT, feature.getAsWKT()));
			if (result.next())
			{
				return result.getInt(1);
			}
			else 
			{
				return -1;
			}
			
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}
		finally
		{
			DBConnection.close(result, statement, conn);
		}		
	}
	
	@Override
	public int verifyLineSegmentExistenceByGeom(LineSegment feature) throws GeoInfoException {
		Connection conn = null;
		Statement statement = null;
		ResultSet result = null;
		if ( feature == null )
		{
			throw PersistentExceptions.LINE_SEGMENT_EXISTS.getExcepcion(LOGGER, null);
		}
		try {
			int lineSegmentId = LineSegment.DEFAULT_LINE_SEGMENT_ID;
			//Verificar existencia de nodo inicial
			int beginNodeId = verifyPointExistence(feature.getBeginNode());
			//Verificar existencia de nodo final
			int endNodeId = verifyPointExistence(feature.getEndNode());			
			if ( beginNodeId != Point.DEFAULT_POINT_ID && endNodeId != Point.DEFAULT_POINT_ID )
			{
				conn = connectionProvider.getConnection();
				statement = conn.createStatement();
				result = statement.executeQuery(MessageFormat.format(EXISTS_LINE_BY_POINT_ID, beginNodeId, endNodeId));
				if (result.next())
				{
					lineSegmentId = result.getInt(1);
				}
			}
			return lineSegmentId;			
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}
		finally
		{
			DBConnection.close(result, statement, conn);
		}		
	}

	@Override
	public int verifyLineSegmentExistenceByid(LineSegment feature)
			throws GeoInfoException {
		Connection conn = null;
		Statement statement = null;
		ResultSet result = null;
		if ( feature == null )
		{
			throw PersistentExceptions.LINE_SEGMENT_IS_NULL.getExcepcion(LOGGER, null);
		}
		try {
			int pointId = Point.DEFAULT_POINT_ID;
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			result = statement.executeQuery(MessageFormat.format(EXISTS_LINE_BY_POINT_ID, feature.getBeginNode().getId(), feature.getEndNode().getId()));
			if (result.next())
			{
				pointId = result.getInt(1);
			}
			return pointId;			
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}
		finally
		{
			DBConnection.close(result, statement, conn);
		}		
	}

	@Override
	public Point retrievePoint(int id) throws GeoInfoException{
		Connection conn = null;
		Statement statement = null;
		ResultSet result = null;
		Point point = null;
		if ( id == Point.DEFAULT_POINT_ID )
		{
			throw PersistentExceptions.INVALID_POINT_ID.getExcepcion(LOGGER, null);
		}
		try {
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			result = statement.executeQuery(MessageFormat.format(RETRIEVE_POINT_BY_ID, id));
			if (result.next())
			{				
				PGgeometry pgPoint = (PGgeometry) result.getObject("po_geom");
				point = GeometryFactory.createPoint(pgPoint);
				point.setId(id);				
			}
			else
			{
				throw PersistentExceptions.POINT_NOT_EXISTS.getExcepcion(LOGGER, null, id);
			}
			return point;			
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}
		finally
		{
			DBConnection.close(result, statement, conn);
		}
	}

	@Override
	public LineSegment retrieveLineSegment(int id)  throws GeoInfoException{
		Connection conn = null;
		Statement statement = null;
		ResultSet result = null;
		LineSegment lineSegment = null;
		if ( id == LineSegment.DEFAULT_LINE_SEGMENT_ID )
		{
			throw PersistentExceptions.INVALID_LINE_SEGMENT_ID.getExcepcion(LOGGER, null);
		}
		try {
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			result = statement.executeQuery(MessageFormat.format(RETRIEVE_LINE_SEGMENT, id));
			if (result.next())
			{
				int beginNodeId = result.getInt("begin_node_id");
				int endNodeId = result.getInt("end_node_id");
				PGgeometry beginNode = (PGgeometry) result.getObject("begin_node_geom");
				PGgeometry endNode = (PGgeometry) result.getObject("end_node_geom");
				Point beginPoint = GeometryFactory.createPoint(beginNode);
				Point endPoint = GeometryFactory.createPoint(endNode);
				beginPoint.setId(beginNodeId);
				endPoint.setId(endNodeId);
				lineSegment = GeometryFactory.createLineSegment(id, beginPoint, endPoint);
			}	
			else
			{
				throw PersistentExceptions.LINE_SEGMENT_NOT_EXISTS.getExcepcion(LOGGER, null, id);
			}
			return lineSegment;
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}
		finally
		{
			DBConnection.close(result, statement, conn);
		}		
	}

	@Override
	public void populatePointId(Point point)  throws GeoInfoException{
		int id = verifyPointExistence(point);		
		if (id == Point.DEFAULT_POINT_ID)
		{
			throw PersistentExceptions.POINT_NOT_EXISTS.getExcepcion(LOGGER, null, point.toString());
		}
		point.setId(id);
		
	}

	@Override
	public void populateLineSegmentId( LineSegment lineSegment )  throws GeoInfoException{
		int id = verifyLineSegmentExistenceByGeom(lineSegment);		
		if (id == LineSegment.DEFAULT_LINE_SEGMENT_ID)
		{
			throw PersistentExceptions.LINE_SEGMENT_NOT_EXISTS.getExcepcion(LOGGER, null, lineSegment.toString());
		}
		lineSegment.setId(id);	
	}

	@Override
	public void populatePointGeom(Point point)  throws GeoInfoException{
		Connection conn = null;
		Statement statement = null;
		ResultSet result = null;
		int id = point.getId();
		if ( id == Point.DEFAULT_POINT_ID )
		{
			throw PersistentExceptions.INVALID_POINT_ID.getExcepcion(LOGGER, null);
		}
		try {
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			result = statement.executeQuery(MessageFormat.format(RETRIEVE_POINT_BY_ID, id));
			if (result.next())
			{				
				PGgeometry pgPoint = (PGgeometry) result.getObject("po_geom");
				point = GeometryFactory.createPoint(pgPoint);
				point.setId(id);
			}			
			else
			{
				throw PersistentExceptions.POINT_NOT_EXISTS.getExcepcion(LOGGER, null, point.getId());
			}
			
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}
		finally
		{
			DBConnection.close(result, statement, conn);
		}		
	}

	@Override
	public void populateLineSegmentGeom(LineSegment lineSegment)  throws GeoInfoException{
		LineSegment line = retrieveLineSegment(lineSegment.getId());
		lineSegment.setCoordinates(line.getBeginNode(), line.getEndNode());		
	}

	@Override
	public List<Point> retrievePoints(int geomDomainId) throws GeoInfoException {		
		Connection conn = null;
		Statement statement = null;
		ResultSet result = null;		
		try {
			List<Point> points = new ArrayList<Point>();
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			result = statement.executeQuery(MessageFormat.format(RETRIEVE_POINTS, geomDomainId));
			while (result.next())
			{	
				PGgeometry pgPoint = (PGgeometry) result.getObject("po_geom");
				Point point = GeometryFactory.createPoint(pgPoint);
				point.setId(result.getInt("fe_id"));
				points.add(point);
			}			
			return points;			
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}
		finally
		{
			DBConnection.close(result, statement, conn);
		}
	}

	@Override
	public List<LineSegment> retrieveLineSegments(int geomDomainId)
			throws GeoInfoException {
		Connection conn = null;
		Statement statement = null;
		ResultSet result = null;		
		try {
			List<LineSegment> lineSegments = new ArrayList<LineSegment>();
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			result = statement.executeQuery(MessageFormat.format(RETRIEVE_LINE_SEGMENTS, geomDomainId));
			while (result.next())
			{	
				int beginNodeId = result.getInt("begin_node_id");
				int endNodeId = result.getInt("end_node_id");
				PGgeometry beginNode = (PGgeometry) result.getObject("begin_node_geom");
				PGgeometry endNode = (PGgeometry) result.getObject("end_node_geom");
				int feId =  result.getInt("fe_id");
				Point beginPoint = GeometryFactory.createPoint(beginNode);
				Point endPoint = GeometryFactory.createPoint(endNode);
				beginPoint.setId(beginNodeId);
				endPoint.setId(endNodeId);
				LineSegment lineSegment = GeometryFactory.createLineSegment(feId, beginPoint, endPoint);
				lineSegments.add(lineSegment);
			}			
			return lineSegments;			
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}
		finally
		{
			DBConnection.close(result, statement, conn);
		}
	}

	@Override
	public void deleteUnreferencedPoints() throws GeoInfoException {
		Connection conn = null;
		Statement statement = null;
		try {
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			statement.executeUpdate(DELETE_UNREF_POINTS);			
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}
		finally
		{
			DBConnection.close(statement, conn);
		}				
	}

	@Override
	public void deleteUnreferencedLineSegments() throws GeoInfoException {
		Connection conn = null;
		Statement statement = null;
		try {
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			statement.executeUpdate(DELETE_UNREF_LINE_SEGMENTS);			
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}
		finally
		{
			DBConnection.close(statement, conn);
		}		
	}
	
}
